﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ForkCarRelationManager.db;
using System.Data;
namespace ForkCarRelationManager.Business
{
    class RepireBI
    {
        DataPaging RepirePage;
        DataSet RepireDataSet;
        dbOperate dbo = new dbOperate();
        /*
         * 获取主页分页页面
         * */
        public DataPaging GetRepirepage(int pagecount)
        {
            RepirePage = new DataPaging("RepireInfor r", pagecount, "(select sum(1) from (select ID from RepireInfor  where Ifhandle=0 order by ID) t where t.ID<=r.ID ) as 序号,r.ClientName,r.ScompanyName,r.ContactName,r.ContactPhone,r.ClientPlace,r.RepairContent,r.RequiredDate,r.RepairDate,iif(r.Ifhandle=0,'处理',iif(r.Ifhandle=1,'已处理')) as Ifhand ,r.Ifhandle,r.ClientID,r.Id as Id", "r.Ifhandle=0", "r");
            return RepirePage;
        }
        /*
         * 获取详情分页页面
         * */
        public DataPaging GetRepiredepage(int pagecount)
        {
            RepirePage = new DataPaging("RepireInfor r", pagecount, "(select sum(1) from (select ID from RepireInfor   order by ID) t where t.ID<=r.ID ) as 序号,r.ClientName,r.ScompanyName,r.ContactName,r.ContactPhone,r.ClientPlace,r.RepairContent,r.RequiredDate,r.RepairDate,r.GiveUpReson,iif(r.Ifhandle=0,'升级',iif(r.Ifhandle=1,'已升级',iif(r.Ifhandle=2,'已放弃'))) as Ifhand,r.Ifhandle,iif(r.Ifhandle=2,'已放弃',iif(r.Ifhandle=0,'放弃',iif(r.Ifhandle=1,'已升级'))) as Ifgiveup,r.ClientID,r.Id as Id", " r.ID=r.ID", "r");
            return RepirePage;
        }
        public bool addRepire(string ClientName,string ScompanyName, string ContactName, string ContactPhone, string ClientPlace, int ClientID, string RepairContent, string RequiredDate, DateTime RepairDate)
        {
            string sql = "insert into RepireInfor(ClientName,ScompanyName,ContactName,ContactPhone,ClientPlace,ClientID,RepairContent,RequiredDate,RepairDate,Ifhandle,GiveUpReson) Values ('" + ClientName + "','"+ScompanyName+"','" + ContactName + "','" + ContactPhone + "','" + ClientPlace + "'," + ClientID + ",'" + RepairContent + "','" + RequiredDate + "',#" + RepairDate + "#,0,'')";
            return dbo.UpdateAndInsert(sql);
        }
        //更新状态
        public bool updateRepire(int RepireID, int model)
        {
            string sql = "update RepireInfor set  Ifhandle =" + model+" where ID="+RepireID;
            return dbo.UpdateAndInsert(sql);
        }
        public DataTable getRepiredata(int RepireID)
        {
            string sql = "select * from RepireInfor where ID=" + RepireID;
            return dbo.GetDate(sql);
        }
        public bool updateRepiredetails(int RepireID, string ClientPlace, string RepairContent, string RequiredDate)
        {
            string sql = "update RepireInfor set  ClientPlace ='" + ClientPlace + "',RepairContent='" + RepairContent + "',RequiredDate='" + RequiredDate + "' where ID=" + RepireID;
            return dbo.UpdateAndInsert(sql);
        }
        public bool updateRepiredbyclientID(int ClientID, string ClientName, string ScompanyName, string ContactName, string ClientPlace, string ContactPhone)
        {
            string sql = "update RepireInfor set  ClientName ='" + ClientName + "',ScompanyName='" + ScompanyName + "',ContactName='" + ContactName + "',ClientPlace='" + ClientPlace + "',ContactPhone='" + ContactPhone + "' where ClientID=" + ClientID;
            return dbo.UpdateAndInsert(sql);
        }
        /*
         * 模糊查询
         * */
        public DataPaging fuzzysearch(int pagecount, string condition)
        {
            string conditionsql = "";
            string filedconditionsql = "";
            if (condition.Trim() != "" && condition != null)
            {
                conditionsql += " and (r.ClientName Like '%" + condition + "%' ";
                filedconditionsql += " and (b.ClientName Like '%" + condition + "%' ";
                conditionsql += " or  r.ContactName Like '%" + condition + "%' ";
                filedconditionsql += " or  b.ContactName Like '%" + condition + "%' ";
                conditionsql += " or  r.ContactPhone Like '%" + condition + "%' ";
                filedconditionsql += " or  b.ContactPhone Like '%" + condition + "%' ";
                conditionsql += " or  r.RepairContent Like '%" + condition + "%') ";
                filedconditionsql += " or  b.RepairContent Like '%" + condition + "%') ";

            }

            RepirePage = new DataPaging("RepireInfor r", pagecount, "(select sum(1) from (select b.ID from RepireInfor b where b.ID=b.ID " + filedconditionsql+"  order by b.ID) t where t.ID<=r.ID ) as 序号,r.ClientName,r.ScompanyName,r.ContactName,r.ContactPhone,r.ClientPlace,r.RepairContent,r.RequiredDate,r.RepairDate,r.GiveUpReson,iif(r.Ifhandle=0,'升级',iif(r.Ifhandle=1,'已升级',iif(r.Ifhandle=2,'已放弃'))) as Ifhandle,iif(r.Ifhandle=2,'已放弃',iif(r.Ifhandle=0,'放弃')) as Ifgiveup,r.ClientID,r.Id as Id", " r.ID=r.ID "+conditionsql, "r");
            return RepirePage;


          
        }
        /*
        * 分公司筛选
        * */
        public DataPaging SearchBycompany(int pagecount, string condition, string scompanyname)
        {
            string conditionsql = "";
            string filedconditionsql = "";
            if (condition.Trim() != "" && condition != null && scompanyname.Trim() != "" && scompanyname.Trim() != null)
            {

                conditionsql += " and (r.ClientName Like '%" + condition + "%' ";
                filedconditionsql += " and (b.ClientName Like '%" + condition + "%' ";
                conditionsql += " or  r.ContactName Like '%" + condition + "%' ";
                filedconditionsql += " or  b.ContactName Like '%" + condition + "%' ";
                conditionsql += " or  r.ContactPhone Like '%" + condition + "%' ";
                filedconditionsql += " or  b.ContactPhone Like '%" + condition + "%' ";
                conditionsql += " or  r.RepairContent Like '%" + condition + "%') ";
                filedconditionsql += " or  b.RepairContent Like '%" + condition + "%') ";
                conditionsql += " and r.ScompanyName='" + scompanyname + "' ";
                filedconditionsql += " and b.ScompanyName='" + scompanyname + "' ";
            }
            else if (condition.Trim() == "" && scompanyname.Trim() != "" && scompanyname.Trim() != null)
            {
                conditionsql += " and r.ScompanyName='" + scompanyname + "' ";
                filedconditionsql += " and b.ScompanyName='" + scompanyname + "' ";
            }
            RepirePage = new DataPaging("RepireInfor r", pagecount, "(select sum(1) from (select b.ID from RepireInfor b where b.ID=b.ID " + filedconditionsql + "  order by b.ID) t where t.ID<=r.ID ) as 序号,r.ClientName,r.ScompanyName,r.ContactName,r.ContactPhone,r.ClientPlace,r.RepairContent,r.RequiredDate,r.RepairDate,r.GiveUpReson,iif(r.Ifhandle=0,'升级',iif(r.Ifhandle=1,'已升级',iif(r.Ifhandle=2,'已放弃'))) as Ifhandle,iif(r.Ifhandle=2,'已放弃',iif(r.Ifhandle=0,'放弃')) as Ifgiveup,r.ClientID,r.Id as Id", " r.ID=r.ID " + conditionsql, "r");
            return RepirePage;
        }
        /*
   * 按时间筛选
   * */
        public DataPaging Searchbytime(int pagecount, string condition, string scompanyname, string begintime, string endtime)
        {
            string conditionsql = "";
            string filedconditionsql = "";
            if (condition.Trim() != "" && scompanyname.Trim() != "")//都有的情况
            {
                conditionsql += " and (r.ClientName Like '%" + condition + "%' ";
                filedconditionsql += " and (b.ClientName Like '%" + condition + "%' ";
                conditionsql += " or  r.ContactName Like '%" + condition + "%' ";
                filedconditionsql += " or  b.ContactName Like '%" + condition + "%' ";
                conditionsql += " or  r.ContactPhone Like '%" + condition + "%' ";
                filedconditionsql += " or  b.ContactPhone Like '%" + condition + "%' ";
                conditionsql += " or  r.RepairContent Like '%" + condition + "%') ";
                filedconditionsql += " or  b.RepairContent Like '%" + condition + "%') ";
                conditionsql += " and r.ScompanyName='" + scompanyname + "' ";
                filedconditionsql += " and b.ScompanyName='" + scompanyname + "' ";
            }


            else if (condition.Trim() == "" && scompanyname.Trim() != "")//没有condition有公司没有人员情况
            {
                conditionsql += " and r.ScompanyName='" + scompanyname + "' ";
                filedconditionsql += " and b.ScompanyName='" + scompanyname + "' ";
            }
            else if (condition.Trim() != "" && scompanyname.Trim() == "")//只有condition情况
            {
                conditionsql += " and (r.ClientName Like '%" + condition + "%' ";
                filedconditionsql += " and (b.ClientName Like '%" + condition + "%' ";
                conditionsql += " or  r.ContactName Like '%" + condition + "%' ";
                filedconditionsql += " or  b.ContactName Like '%" + condition + "%' ";
                conditionsql += " or  r.ContactPhone Like '%" + condition + "%' ";
                filedconditionsql += " or  b.ContactPhone Like '%" + condition + "%' ";
                conditionsql += " or  r.RepairContent Like '%" + condition + "%') ";
                filedconditionsql += " or  b.RepairContent Like '%" + condition + "%') ";
            }
            conditionsql += " and r.RepairDate between #" + begintime + " 0:00:00# and #" + endtime + " 23:59:59# ";
            filedconditionsql += " and b.RepairDate between #" + begintime + " 0:00:00# and #" + endtime + " 23:59:59# ";

            RepirePage = new DataPaging("RepireInfor r", pagecount, "(select sum(1) from (select b.ID from RepireInfor b where b.ID=b.ID " + filedconditionsql + "  order by b.ID) t where t.ID<=r.ID ) as 序号,r.ClientName,r.ScompanyName,r.ContactName,r.ContactPhone,r.ClientPlace,r.RepairContent,r.RequiredDate,r.RepairDate,r.GiveUpReson,iif(r.Ifhandle=0,'升级',iif(r.Ifhandle=1,'已升级',iif(r.Ifhandle=2,'已放弃'))) as Ifhandle,iif(r.Ifhandle=2,'已放弃',iif(r.Ifhandle=0,'放弃')) as Ifgiveup,r.ClientID,r.Id as Id", " r.ID=r.ID " + conditionsql, "r");
            return RepirePage;
        }
        public int GetmaintenIDbyrepireID(int repireID)
        {
            string sql = "select ID from MaintenRecord where RepireID=" + repireID;
           int maintenID=Convert.ToInt32( dbo.GetOneData(sql));
           return maintenID;

        }
    }
}
